﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Common.Logging;

namespace InfoboardAdapterC.Common
{
    public class SqlServerDbConnection
    {
        private static readonly ILog _loggerLog = LogManager.GetLogger<SqlServerDbConnection>();
        /// <summary>
        /// 获取局域网内的所有数据库服务器名称
        /// </summary>
        /// <returns>服务器名称数组</returns>
        public List<string> GetSqlServerNames()
        {
            var dbDataSourceEnumerator = SqlClientFactory.Instance.CreateDataSourceEnumerator();
            if (dbDataSourceEnumerator != null)
            {
                var dataSources = dbDataSourceEnumerator.GetDataSources();

                var column = dataSources.Columns["InstanceName"];
                var column2 = dataSources.Columns["ServerName"];

                var rows = dataSources.Rows;
                var serverlist = new List<string>();
                for (var i = 0; i < rows.Count; i++)
                {
                    var str2 = rows[i][column2] as string;
                    var str = rows[i][column] as string;
                    string array;
                    if ((str == null) || (str.Length == 0) || ("MSSQLSERVER" == str))
                    {
                        array = str2;
                    }
                    else
                    {
                        array = str2 + @"/" + str;
                    }

                    serverlist.Add(array);
                }

                serverlist.Sort();

                return serverlist;
            }
            throw new NullReferenceException("获取dbDataSourceEnumeratora值为空");
        }

        /// <summary>
        /// 获取查询数据库连接字符串
        /// </summary>
        /// <param name="dbServerName">服务器名</param>
        /// <param name="dbUserName">用户名</param>
        /// <param name="dbPassword">密码</param>
        /// <param name="dbName">数据库名称</param>
        /// <returns></returns>
        public string DatabaseConnectionString(string dbServerName, string dbUserName, string dbPassword,
            string dbName = "")
        {
            if (dbServerName.Length == 0)
                throw new ArgumentNullException(nameof(dbServerName));
            if (dbUserName.Length == 0)
                throw new ArgumentNullException(nameof(dbUserName));
            if (dbPassword.Length == 0)
                throw new ArgumentNullException(nameof(dbPassword));
            var dbString = "";
            if (dbName.Length > 0)
                dbString = $"Initial Catalog={dbName};";
            return
                $"Data Source={dbServerName};{dbString}{dbString}User ID={dbUserName};Password={dbPassword}";
        }

        /// <summary>
        /// 查询sql中的非系统库
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <returns></returns>
        public List<string> DatabaseList(string connection)
        {
            var getCataList = new List<string>();
            const string cmdStirng = "select name from sys.databases where database_id > 4";
            var connect = new SqlConnection(connection);
            var cmd = new SqlCommand(cmdStirng, connect);
            try
            {
                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                    IDataReader dr = cmd.ExecuteReader();
                    getCataList.Clear();
                    while (dr.Read())
                    {
                        getCataList.Add(dr["name"].ToString());
                    }
                    dr.Close();
                }

            }
            catch (SqlException e)
            {
                _loggerLog.Error($"{e.Message},连接语句[{connection}]");
            }
            finally
            {
                if (connect.State == ConnectionState.Open)
                {
                    connect.Dispose();
                }
            }
            return getCataList;
        }

        /// <summary>
        /// 获取列名
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<string> GetTables(string connection)
        {
            var tablelist = new List<string>();
            var objConnetion = new SqlConnection(connection);
            try
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                    var objTable = objConnetion.GetSchema("Tables");
                    tablelist.AddRange(from DataRow row in objTable.Rows select row[2].ToString());
                }
            }
            catch (SqlException e)
            {
                _loggerLog.Error(e.Message);
            }
            finally
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Dispose();
                }

            }
            return tablelist;
        }

        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<string> GetColumnField(string connection, string tableName)
        {
            var columnlist = new List<string>();
            var objConnetion = new SqlConnection(connection);
            try
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }

                var cmd = new SqlCommand("Select Name FROM SysColumns Where id=Object_Id('" + tableName + "')", objConnetion);
                var objReader = cmd.ExecuteReader();

                while (objReader.Read())
                {
                    columnlist.Add(objReader[0].ToString());

                }
            }
            catch (SqlException e)
            {
                _loggerLog.Error(e.Message);
            }
            objConnetion.Close();
            return columnlist;
        }
        /// <summary>
        /// 测试连接数据库是否成功
        /// </summary>
        /// <returns></returns>
        public bool SqlConnectionTest(string connectionString)
        {
            //判断连接变量
            bool isCanConnectioned;
            //创建连接对象
            var sqlConnection = new SqlConnection(connectionString);
            //ConnectionTimeout 在.net 1.x 可以设置 在.net 2.0后是只读属性，则需要在连接字符串设置
            //如：server=.;uid=sa;pwd=;database=PMIS;Integrated Security=SSPI; Connection Timeout=30
            try
            {
                //Open DataBase
                //打开数据库
                sqlConnection.Open();
                isCanConnectioned = true;
            }
            catch
            {
                //Can not Open DataBase
                //打开不成功 则连接不成功
                isCanConnectioned = false;
            }
            finally
            {
                //Close DataBase
                //关闭数据库连接
                sqlConnection.Close();
            }
            //Connection   is   available  
            return isCanConnectioned;
        }
    }
}
